import time 
import numpy as np
import pymysql 
import pandas as pd 
from datetime import datetime

import pymysql.cursors


class MysqlUtils(object):
    def __init__(self):
        self.conn= pymysql.connect(
            host='localhost',
            user="root",
            passwd="MYSQL123",
            db="scenic",
            port=3306,
            charset="utf8"
        )
    def get_scenic_data(self):
        """"
        获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = '''
        SELECT 
            t.tourist_agency_name,
            rel.id_no,left(rel.id_no,2) as province_code,
            DAYOFWEEK(gate.create_time) as 'non_weekend', 
            CAST(SUBSTRING(rel.id_no,7,4) as UNSIGNED) as age
        FROM ticket_order_user_rel rel
        LEFT JOIN ticket_order t on t.id = rel.order_id 
        LEFT JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time IS NOT NULL;
        '''
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head)
        
        
        #数据清洗
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1,7] else 0)
        
        #新增有效标记列，即记录是否有id_no
        df['valid_id'] = df['id_no'].apply(lambda x : 1 if str(x).split != '' else 0)
        
        #计算比例
        df['out_province_ratio'] = df.apply(
            lambda x : 1 if(x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan, axis = 1)
        df['elderly_ratio'] = df.apply(
            lambda x : 1 if (x['valid_id'] and 2025 - int(x['age']) >= 60) else 0 if x['valid_id'] else np.nan, axis = 1)
        
        print(df.head)
        
    #分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            out_province=('out_province_ratio','sum'),
            elderly=('elderly_ratio','sum'),
            non_weekend_ratio=('non_weekend','mean')
        ).reset_index()
        
        #计算实际比例
        result['out_province_retio'] = result['out_province'] / result['valid_visitors'].replace(0,np.nan)
        result['elderly_retio'] = result['elderly'] / result['valid_visitors'].replace(0,np.nan)
        
        #清除中间列
        result = result.drop(['out_province','elderly'],axis=1)
        result['out_province_retio'] = result['out_province_retio'].fillna(0)
        result['elderly_retio'] = result['elderly_retio'].fillna(0)
        
        result.to_csv('scenic_data.csv')
    
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()